package com.red.wood.module.ircs.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.module.ircs.dao.IrcsDataInfoDao;

@Repository
public class IrcsDataInfoDaoImpl implements IrcsDataInfoDao {

	private static final Log log = LogFactory.getLog(IrcsDataInfoDaoImpl.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(Map<String, Object> param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = filter(listPageData(param));
		count = listCount(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public List<Map<String, Object>> getDetailInfo(String ircsId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.ircsName,t1.corp,t1.ircsId,t1.ircsAdd,t2.name,t2.idType,t2.id,t2.tel,t2.mobile,t2.email,t3.houseOfficeName,t3.houseOfficeIdType,t3.houseOfficeId,t3.houseOfficeTel,t3.houseOfficeMobile,t3.houseOfficeEmail");
		sql.append(" from t_ircs_ircsdata t1 ");
		sql.append(" left join t_ircs_emergencycontact t2 on t1.ircsId = t2.ircsId");
		sql.append(" left join t_ircs_houseinfo_houseofficer t3 on t1.ircsId = t3.ircsId");
		sql.append(" where t1.ircsId = '"+ircsId+"' limit 1");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> listPageData(Map<String, Object> param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {//初级权限
			//sql = getPrimaryLevelSql(param);
			sql.append("select ircs.province,ircs.ircsId,ircs.ircsName,house.houseNum,ircs.ipNum,ircs.totalBandWidth,ircs.userNum,ircs.domainNum,ircs.publicDomainNum ");
			sql.append(" from t_ircs_data_statistics ircs ");
			sql.append(" left join (select count(1) houseNum, house.ircsId");
			sql.append("              from t_ircs_houseinfo house");
			sql.append("             where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)");
			sql.append("            group by house.ircsId) house");
			sql.append(" on ircs.ircsId = house.ircsId");
			sql.append(" where ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else if(StringUtils.equals(userLevel, "2")) {//中级权限
			//sql = getMiddleLevelSql(param);
			sql.append("select province,ircsId,ircsName,houseNum,ipNum,totalBandWidth,userNum,domainNum,publicDomainNum from t_ircs_data_statistics ircs where 1 = 1");
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else {//高级权限
			sql.append("select ircs.province,ircs.ircsId,ircs.ircsName,ircs.houseNum,ircs.ipNum,ircs.totalBandWidth,ircs.userNum,ircs.domainNum,ircs.publicDomainNum");
			sql.append(" from t_ircs_data_statistics ircs ");
			sql.append(" where ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(" select ircs.province,ircs.ircsId,ircs.ircsName,ircs.houseNum,ircs.ipNum,ircs.totalBandWidth,ircs.userNum,ircs.domainNum,ircs.publicDomainNum ");
			sql.append(" from t_ircs_data_statistics ircs");
			sql.append(" where ircs.province != '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" and ircsId in (select distinct house.ircsId from t_ircs_houseinfo house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
		}
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String listCount(Map<String, Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		
		if(StringUtils.equals(userLevel, "1") || StringUtils.equals(userLevel, "2")) {
			sql.append("select count(*) count from t_ircs_data_statistics ircs where 1 = 1");
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else if(StringUtils.equals(userLevel, "3")) {
			sql.append("select sum(count) count from(");
			sql.append("select count(1) count");
			sql.append(" from t_ircs_data_statistics ircs ");
			sql.append(" where ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(" select count(1) count");
			sql.append(" from t_ircs_data_statistics ircs");
			sql.append(" where ircs.province != '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" and ircsId in (select distinct house.ircsId from t_ircs_houseinfo house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
			sql.append(") t");
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}

	/**
	 * 初级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getPrimaryLevelSql(Map<String,Object> param) {
		StringBuffer sql = new StringBuffer();
		//type为2标记为机房是本省，企业为外省的情况
		sql.append("select 2 as type,ircs.ircsId,ircs.ircsName,house.houseNum,ip.ipNum,gateway.totalBandWidth,userinfo.userNum,domain.domainNum,publicDomain.publicDomainNum");
		sql.append(" from t_ircs_ircsdata ircs");
		sql.append(" left join (select count(1) houseNum,house.ircsId,(select province from provinces t where t.provinceid = house.houseProvince limit 1) as province from t_ircs_houseinfo house group by house.ircsId,house.houseProvince) house");
		sql.append(" on ircs.ircsId = house.ircsId and ircs.province = house.province");
		sql.append(" left join (select count(1) ipNum,ip.ircsId,ip.province from t_ircs_ipinfo ip group by ip.ircsId,ip.province) ip");
		sql.append(" on ircs.ircsId = ip.ircsId and ircs.province = ip.province");
		sql.append(" left join (select sum(gateway.bandWidth) totalBandWidth,gateway.ircsId,gateway.province from t_ircs_gatewayinfo gateway group by gateway.ircsId,gateway.province) gateway");
		sql.append(" on ircs.ircsId = gateway.ircsId and ircs.province = gateway.province");
		sql.append(" left join (select count(1) userNum,userinfo.ircsId,userinfo.province from t_ircs_queryUserInfo userinfo group by userinfo.ircsId,userinfo.province) userinfo");
		sql.append(" on ircs.ircsId = userinfo.ircsId and ircs.province = userinfo.province");
		sql.append(" left join (select count(1) domainNum,domain.ircsId,userinfo.province from t_ircs_userinfo_serviceinfo_domain domain");
		sql.append("             inner join (select userinfo.ircsId, userinfo.userId, userinfo.province from t_ircs_queryUserInfo userinfo group by userinfo.ircsId, userinfo.userId, userinfo.province) userinfo");
		sql.append("             on domain.ircsId = userinfo.ircsId and domain.userId = userinfo.userId group by domain.ircsId, userinfo.province) domain");
		sql.append(" on ircs.ircsId = domain.ircsId and ircs.province = domain.province");
		sql.append(" left join (select count(1) publicDomainNum,publicDomain.ircsId from t_ircs_publicDomainInfo publicDomain group by publicDomain.ircsId) publicDomain");
		sql.append(" on ircs.ircsId = publicDomain.ircsId");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	/**
	 * 中级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getMiddleLevelSql(Map<String,Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("select 1 as type,ircs.ircsId,ircs.ircsName,house.houseNum,ip.ipNum,gateway.totalBandWidth,userinfo.userNum,domain.domainNum,publicDomain.publicDomainNum");
		sql.append(" from t_ircs_ircsdata ircs");
		sql.append(" left join (select count(1) houseNum,house.ircsId from t_ircs_houseinfo house group by house.ircsId) house");
		sql.append(" on ircs.ircsId = house.ircsId");
		sql.append(" left join (select count(1) ipNum,ip.ircsId from t_ircs_ipinfo ip group by ip.ircsId) ip");
		sql.append(" on ircs.ircsId = ip.ircsId");
		sql.append(" left join (select sum(gateway.bandWidth) totalBandWidth,gateway.ircsId from t_ircs_gatewayinfo gateway group by gateway.ircsId) gateway");
		sql.append(" on ircs.ircsId = gateway.ircsId");
		sql.append(" left join (select count(1) userNum,userinfo.ircsId from t_ircs_userinfo_result userinfo group by userinfo.ircsId) userinfo");
		sql.append(" on ircs.ircsId = userinfo.ircsId");
		sql.append(" left join (select count(1) domainNum, domain.ircsId from t_ircs_userinfo_serviceinfo_domain domain group by domain.ircsId) domain");
		sql.append(" on ircs.ircsId = domain.ircsId");
		sql.append(" left join (select count(1) publicDomainNum,publicDomain.ircsId from t_ircs_publicDomainInfo publicDomain group by publicDomain.ircsId) publicDomain");
		sql.append(" on ircs.ircsId = publicDomain.ircsId");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	/**
	 * 高级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getHightLevelSql(Map<String,Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		//type为2标记为机房是本省，企业为外省的情况
		sql.append("select 2 as type,                                                                   ");
		sql.append("       house.ircsId,                                                                ");
		sql.append("       house.ircsName,                                                              ");
		sql.append("       house.houseNum,                                                              ");
		sql.append("       ip.ipNum,                                                                    ");
		sql.append("       gateway.totalBandWidth,                                                      ");
		sql.append("       userinfo.userNum,                                                            ");
		sql.append("       domain.domainNum,                                                            ");
		sql.append("       publicDomain.publicDomainNum                                                 ");
		sql.append("  from (select house.ircsId,                                                        ");
		sql.append("               ircs.ircsName,                                                       ");
		sql.append("               ircs.province,                                                       ");
		sql.append("               (select province                                                     ");
		sql.append("                  from provinces t                                                  ");
		sql.append("                 where t.provinceid = house.houseProvince limit 1) as houseProvince,");
		sql.append("               count(*) as houseNum                                                 ");
		sql.append("          from t_ircs_houseinfo house                                               ");
		sql.append("         inner join t_ircs_ircsdata ircs                                            ");
		sql.append("            on house.ircsId = ircs.ircsId                                           ");
		sql.append("         where 1 = 1                                                                ");
		sql.append("           and ircs.province != '"+userProvince+"'                                  ");
		sql.append("           and house.houseProvince =                                                ");
		sql.append("               (select provinceid                                                   ");
		sql.append("                  from provinces t                                                  ");
		sql.append("                 where t.province = '"+userProvince+"' limit 1)                     ");
		sql.append("         group by house.ircsId,                                                     ");
		sql.append("                  ircs.ircsName,                                                    ");
		sql.append("                  ircs.province,                                                    ");
		sql.append("                  house.houseProvince) house                                        ");
		sql.append("  left join (select ircs.ircsId,                                                    ");
		sql.append("                    ircs.province,                                                  ");
		sql.append("                    ip.province as ipProvince,                                      ");
		sql.append("                    count(*) as ipNum                                               ");
		sql.append("               from t_ircs_ipinfo ip                                                ");
		sql.append("              inner join t_ircs_ircsdata ircs                                       ");
		sql.append("                 on ip.ircsId = ircs.ircsId                                         ");
		sql.append("              where 1 = 1                                                           ");
		sql.append("                and ircs.province != '"+userProvince+"'                             ");
		sql.append("                and ip.province = '"+userProvince+"'                                ");
		sql.append("              group by ip.ircsId, ircs.province, ip.province) ip                    ");
		sql.append("    on house.ircsId = ip.ircsId                                                     ");
		sql.append("  left join (select gateway.ircsId,                                                 ");
		sql.append("                    gateway.province,                                               ");
		sql.append("                    sum(gateway.bandWidth) totalBandWidth                           ");
		sql.append("               from t_ircs_gatewayinfo gateway                                      ");
		sql.append("              inner join t_ircs_ircsdata ircs                                       ");
		sql.append("                 on gateway.ircsId = ircs.ircsId                                    ");
		sql.append("                and ircs.province != '"+userProvince+"'                             ");
		sql.append("                and gateway.province = '"+userProvince+"'                           ");
		sql.append("              group by gateway.ircsId, gateway.province) gateway                    ");
		sql.append("    on house.ircsId = gateway.ircsId                                                ");
		sql.append("  left join (select userinfo.ircsId, userinfo.province, count(1) userNum            ");
		sql.append("               from t_ircs_queryUserInfo userinfo                                   ");
		sql.append("              inner join t_ircs_ircsdata ircs                                       ");
		sql.append("                 on userinfo.ircsId = ircs.ircsId                                   ");
		sql.append("                and ircs.province != '"+userProvince+"'                             ");
		sql.append("                and userinfo.province = '"+userProvince+"'                          ");
		sql.append("              group by userinfo.ircsId, userinfo.province) userinfo                 ");
		sql.append("    on house.ircsId = userinfo.ircsId                                               ");
		sql.append("  left join (select domain.ircsId, domain.province, domain.domainNum                ");
		sql.append("               from (select domain.ircsId,                                          ");
		sql.append("                            userinfo.province,                                      ");
		sql.append("                            count(1) domainNum                                      ");
		sql.append("                       from t_ircs_userinfo_serviceinfo_domain domain               ");
		sql.append("                        inner join t_ircs_queryUserInfo userinfo                    ");
		/*sql.append("                      inner join (select userinfo.ircsId,                           ");
		sql.append("                                        userinfo.userId,                            ");
		sql.append("                                        userinfo.province                           ");
		sql.append("                                   from t_ircs_queryUserInfo userinfo               ");
		sql.append("                                  where userinfo.province = '"+userProvince+"'      ");
		sql.append("                                  group by userinfo.ircsId,                         ");
		sql.append("                                           userinfo.userId,                         ");
		sql.append("                                           userinfo.province) userinfo              ");*/
		
		sql.append("                         on domain.ircsId = userinfo.ircsId                         ");
		sql.append("                        and domain.userId = userinfo.userId                         ");
		sql.append("                        and userinfo.province = '"+userProvince+"'"                  );
		sql.append("                      group by domain.ircsId, userinfo.province) domain             ");
		sql.append("              inner join t_ircs_ircsdata ircs                                       ");
		sql.append("                 on domain.ircsId = ircs.ircsId                                     ");
		sql.append("                and ircs.province != '"+userProvince+"') domain                     ");
		sql.append("    on house.ircsId = domain.ircsId                                                 ");
		sql.append("  left join (select publicDomain.ircsId, count(1) publicDomainNum                   ");
		sql.append("               from t_ircs_publicDomainInfo publicDomain                            ");
		sql.append("              group by publicDomain.ircsId) publicDomain                            ");
		sql.append("    on house.ircsId = publicDomain.ircsId                                           ");
		sql.append("                                                                                    ");
		return sql;
	}
	
	public StringBuffer getSqlCount() {
		StringBuffer sql = new StringBuffer();
		sql.append("select 2 as type,ircs.ircsId,ircs.ircsName,0 as houseNum,0 as ipNum,0 as totalBandWidth,0 as userNum,0 as domainNum,0 as publicDomainNum from t_ircs_ircsdata ircs where 1 = 1");
		return sql;
	}
	
	public StringBuffer getQueryConf(Map<String,Object> param) {
		String ircsId = CommonUtils.valueOf(param.get("ircsId"));
		String ircsName = CommonUtils.valueOf(param.get("ircsName"));
		StringBuffer queryConf = new StringBuffer();
		if(StringUtils.isNotBlank(ircsId)) {
			queryConf.append(" and ircs.ircsId like '%"+ircsId+"%'");
		}
		if(StringUtils.isNotBlank(ircsName)) {
			queryConf.append(" and ircs.ircsName like '%"+ircsName+"%'");
		}
		return queryConf;
	}
	
	public List<Map<String, Object>> filter(List<Map<String, Object>> list){
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				map.put("houseNum",CommonUtils.filteNull(map.get("houseNum")));
				map.put("ipNum",CommonUtils.filteNull(map.get("ipNum")));
				map.put("totalBandWidth",CommonUtils.filteNull(map.get("totalBandWidth")));
				map.put("userNum",CommonUtils.filteNull(map.get("userNum")));
				map.put("domainNum",CommonUtils.filteNull(map.get("domainNum")));
				map.put("publicDomainNum",CommonUtils.filteNull(map.get("publicDomainNum")));
			}
		}
		return list;
	}
}
